package org.example.mock;

import lombok.extern.slf4j.Slf4j;
import org.example.bean.MovieReleaseCount;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;

@Slf4j
public class MovieReleaseCountGenerator {
    private static final String DB_URL = "jdbc:mysql://192.168.10.102/changchengfilm?requireSSL=true&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "000000";
    private static final int BATCH_SIZE = 20;
    private static final Random RANDOM = new Random();

    public static void main(String[] args) {
        log.info("开始生成并插入电影上映数量数据（年份2005-2024）...");

        try {
            // 验证数据库连接
            testDatabaseConnection();

            // 生成20条数据（年份2005-2024）
            List<MovieReleaseCount> dataList = generateData();

            // 插入数据库（使用INSERT IGNORE处理重复）
            int insertedCount = insertToDatabase(dataList);
            log.info("数据插入完成，成功插入 {} 条数据", insertedCount);

            // 打印全部数据
            log.info("生成的数据如下：");
            for (MovieReleaseCount data : dataList) {
                log.info(data.toString());
            }

            // 验证插入结果
            verifyInsertionResults();
        } catch (Exception e) {
            log.error("数据生成或插入失败", e);
        }
    }

    /**
     * 测试数据库连接
     */
    private static void testDatabaseConnection() throws SQLException {
        log.info("测试数据库连接...");
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
            log.info("数据库连接成功！");

            // 检查表是否存在
            DatabaseMetaData metaData = conn.getMetaData();
            ResultSet tables = metaData.getTables(null, null, "movie_release_count", null);
            if (tables.next()) {
                log.info("表 'movie_release_count' 存在");

                // 检查表结构
                ResultSet columns = metaData.getColumns(null, null, "movie_release_count", null);
                StringBuilder columnInfo = new StringBuilder("表结构：");
                while (columns.next()) {
                    columnInfo.append(columns.getString("COLUMN_NAME"))
                            .append("(").append(columns.getString("TYPE_NAME")).append("), ");
                }
                log.info(columnInfo.toString());
            } else {
                log.error("表 'movie_release_count' 不存在！");
            }
        } catch (SQLException e) {
            log.error("数据库连接失败，请检查URL、用户名和密码", e);
            throw e;
        }
    }

    /**
     * 生成电影上映数量数据（年份2005-2024）
     */
    private static List<MovieReleaseCount> generateData() {
        List<MovieReleaseCount> result = new ArrayList<>();
        for (int i = 0; i < 20; i++) {
            int year = 2005 + i;

            // 生成当年上映电影数量（随年份增长）
            int baseCount = 50 + (year - 2005) * 8; // 基准数量随年份增长
            int movieCount = baseCount + RANDOM.nextInt(60); // 随机波动±30

            // 总投票数 = 电影数 × 单部平均投票数（随年份增长）
            int avgVotesPerMovie = 1000 + (year - 2005) * 250;
            int totalVotes = movieCount * avgVotesPerMovie;

            MovieReleaseCount data = new MovieReleaseCount(year, movieCount, totalVotes);
            log.debug("生成数据: {}", data);
            result.add(data);
        }
        return result;
    }

    /**
     * 使用INSERT IGNORE批量插入数据，忽略重复记录
     */
    private static int insertToDatabase(List<MovieReleaseCount> dataList) throws SQLException {
        if (dataList.isEmpty()) return 0;

        Connection conn = null;
        PreparedStatement ps = null;
        int insertedCount = 0;

        try {
            log.info("开始插入数据到数据库...");
            conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
            conn.setAutoCommit(false);

            // 使用INSERT IGNORE避免重复数据报错
            String sql = "INSERT IGNORE INTO movie_release_count (release_year, movie_count, total_votes) VALUES (?, ?, ?)";
            ps = conn.prepareStatement(sql);

            for (MovieReleaseCount data : dataList) {
                ps.setInt(1, data.getReleaseYear());
                ps.setInt(2, data.getMovieCount());
                ps.setInt(3, data.getTotalVotes());
                ps.addBatch();
            }

            int[] counts = ps.executeBatch();
            for (int count : counts) {
                if (count > 0) insertedCount++; // 只统计成功插入的记录
            }
            conn.commit();

            log.info("尝试插入 {} 条数据，实际成功插入 {} 条", dataList.size(), insertedCount);
            return insertedCount;
        } catch (SQLException e) {
            if (conn != null) {
                log.info("执行回滚操作...");
                conn.rollback();
            }
            log.error("数据库插入失败，已回滚", e);

            // 检查错误代码
            if (e.getSQLState() != null) {
                log.error("SQLState: {}", e.getSQLState());
            }
            if (e.getErrorCode() != 0) {
                log.error("错误代码: {}", e.getErrorCode());
            }

            throw e;
        } finally {
            if (ps != null) ps.close();
            if (conn != null) {
                conn.setAutoCommit(true); // 恢复自动提交模式
                conn.close();
            }
        }
    }

    /**
     * 验证插入结果
     */
    private static void verifyInsertionResults() {
        log.info("验证插入结果...");
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM movie_release_count")) {

            if (rs.next()) {
                int count = rs.getInt(1);
                log.info("数据库中movie_release_count表的总记录数: {}", count);
            }
        } catch (SQLException e) {
            log.error("验证插入结果失败", e);
        }
    }
}